﻿namespace SubSet
{
    using System;
    using System.Drawing;
    using System.Reflection;
    using System.Windows.Forms;
    using System.Data;
    using Microsoft.Office.Interop.Excel;
   

    internal class CreateExcelDoc
    {
        private Microsoft.Office.Interop.Excel.Application app;
        private Workbook workbook;
        private Worksheet worksheet;
        private Range workSheet_range;

        public CreateExcelDoc(int noSolutions)
        {
           
            {
                this.createDoc(noSolutions);
            }
          
        }
        private string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }
        public void addData(int row, int col, string data, string cell1, string cell2, string format)
        {  cell1 = GetExcelColumnName(col).ToString()+row.ToString();
               cell2 = cell1;
            
           // MessageBox.Show(cell1);
           try
           {
               string[,] saRet = new string[1, 1];
               saRet[0, 0] = data;
            // this.worksheet.Cells[row, col] = data;
              
               this.workSheet_range = this.worksheet.get_Range(cell1, cell2);
               
              //  this.workSheet_range.Borders.Color = Color.Black.ToArgb();
                this.workSheet_range.NumberFormat = format;
               this.workSheet_range.set_Value(Missing.Value, saRet);
            }
           catch (System.Exception ex)
            {
           //     System.Windows.Forms.Application.Exit();
               MessageBox.Show(ex.ToString());
            }
        }
        public void differentaddData(double [,] data, int rowLength, int colLength)
        {
            String cell2 = GetExcelColumnName(colLength).ToString() + rowLength.ToString();
            String cell1 = "A1";

             //MessageBox.Show(cell2);
            try
            {
              //  string[,] saRet = new string[rowLength, colLength];
                //saRet[0, 0] = data;
                // this.worksheet.Cells[row, col] = data;

                this.workSheet_range = this.worksheet.get_Range(cell1, cell2);

                //  this.workSheet_range.Borders.Color = Color.Black.ToArgb();
                this.workSheet_range.NumberFormat = "General;-General;;@";
                this.workSheet_range.set_Value(Missing.Value, data);
               // this.workSheet_range.set_Item
               // this.workSheet_range.Value = "0.0";
            //    foreach (Microsoft.Office.Interop.Excel.Range range in this.workSheet_range)
              //  {
                //    if (range.Value != null)
                 //   {
                  //      int number;
                   //     bool isNumber = int.TryParse(range.Value.ToString().Trim(), out number);
                    //    if (isNumber)
                     //   {
                      //      range.NumberFormat = "#,##0";
                       //     range.Value = number;
                       // }
                       // else
                       // {
                            //the percent values were decimals with commas in the string       
                         //   string temp = range.Value.ToString();
                          //  temp = temp.Replace(",", ".");
                          //  range.Value = temp;
                       // }
                   // }
                //}
            }
            catch (System.Exception ex)
            {
                //     System.Windows.Forms.Application.Exit();
                MessageBox.Show(ex.ToString());
            }
        }


        private void ReleaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }

        public void addSheet(int noSolutions)
        {
            try
            {
                for (int i = 0; i < noSolutions; i++)
                {
                    this.workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                }
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.Application.Exit();
               // MessageBox.Show(ex.ToString());
            }
        }

        public void changeSheetName(int sheet, string change)
        {
            try
            {
                this.selectSheet(sheet);
                this.worksheet.Name = change + sheet.ToString();
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.Application.Exit();
                
            }
        }

        public void changeWorkbookName(string newName)
        {
        }

        public void createDoc(int noSolutions)
        {
            try
            {
                this.app = (Microsoft.Office.Interop.Excel.Application) Activator.CreateInstance(Type.GetTypeFromCLSID(new Guid("00024500-0000-0000-C000-000000000046")));
                this.app.Visible = true;
                this.workbook = this.app.Workbooks.Add(1);
                this.worksheet = (Worksheet) this.workbook.Sheets[1];
                this.addSheet(noSolutions);
                this.selectSheet(100);
            }
            catch (Exception)
            {
                
                Console.Write("Error");
            }
        }

        public void createHeaders(int row, int col, string htext, string cell1, string cell2, int mergeColumns, string b, bool font, int size, string fcolor)
        {
            //return;
            try
            {
              //  this.worksheet.Cells[row, col] = htext;
                this.workSheet_range = this.worksheet.get_Range(cell1, cell2);
                string str = b;
                if (str != null)
                {
                   // if (!(str == "YELLOW"))
                    //{
                      //if (str == "GRAY")
                       // {
                         //   this.workSheet_range.Interior.Color = Color.Gray.ToArgb();
                       // }
                       // else if (str == "GAINSBORO")
                       // {
                            this.workSheet_range.Interior.Color = Color.Turquoise.ToArgb();
                        //}
                        //else if (str == "Turquoise")
                       // {
                         //   this.workSheet_range.Interior.Color = Color.Turquoise.ToArgb();
                       // }
                       // else if (str == "PeachPuff")
                        //{
                          //  this.workSheet_range.Interior.Color = Color.PeachPuff.ToArgb();
                        //}
                    //}
                   // else
                   // {
                     //   this.workSheet_range.Interior.Color = Color.Yellow.ToArgb();
                   // }
               // }
                //this.workSheet_range.Borders.Color = Color.Black.ToArgb();
                //this.workSheet_range.Font.Bold = font;
               // this.workSheet_range.ColumnWidth = size;
               // if (fcolor.Equals(""))
               // {
                 //   this.workSheet_range.Font.Color = Color.White.ToArgb();
                }
               // else
               // {
                    this.workSheet_range.Font.Color = Color.Black.ToArgb();
               // }
            }
           catch (System.Exception ex)
            {
               System.Windows.Forms.Application.Exit();
              //  MessageBox.Show(ex.ToString());
            }
            
        }

        public void selectSheet(int sheet)
        {
            try
            {
                this.worksheet = (Worksheet)this.workbook.Worksheets["Sheet" + sheet];
                this.worksheet.Select(Type.Missing);
            }
            catch (System.Exception ex)
            {
               // System.Windows.Forms.Application.Exit();
              //  MessageBox.Show(ex.ToString());
            }
        }

    }
}

